package org.compiere.process;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.logging.Level;

import org.compiere.model.MClient;
import org.compiere.model.MMailText;
import org.compiere.model.MUser;
import org.compiere.model.X_R_MailText;
import org.compiere.util.DB;
import org.compiere.util.EMail;
import org.compiere.util.Env;

public class CMS_ContractsCheck extends SvrProcess {
	
	@Override
	protected void prepare() {
	}

	@Override
	protected String doIt() throws Exception {
		init();
		return null;
	}
	
	private void init() throws Exception
	{		
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT c.CMS_Contract_ID, EXTRACT(DAY FROM (c.EndDateExecution - CURRENT_DATE)), ");
		sql.append("CASE WHEN c.CMS_Contract_ContractNum IS NULL THEN ' ' ELSE c.CMS_Contract_ContractNum END AS CMS_Contract_ContractNum, ");
		sql.append("c.ResponisbleEmployee_ID FROM Adempiere.CMS_Contract c "); 
		sql.append("INNER JOIN Adempiere.CMS_StatusesType s ON c.CMS_StatusesType_ID = s.CMS_StatusesType_ID WHERE c.isActive = 'Y' AND lower(s.name) LIKE 'дейст%'");
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		ArrayList<Integer> index = new ArrayList<Integer>();
		ArrayList<Integer> time = new ArrayList<Integer>();
		ArrayList<String> documentno = new ArrayList<String>();
		ArrayList<Integer> bpartner = new ArrayList<Integer>();
		
		try
		{
			pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = pstmt.executeQuery();
			
			while(rs.next())
			{
				index.add(rs.getInt(1));
				time.add(rs.getInt(2));
				documentno.add(rs.getString(3));
				bpartner.add(rs.getInt(4));
			}
		}
		catch (Exception e) 
		{
			log.log(Level.SEVERE, "CMS_ContractsCheck: " + e.toString());
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		
		ArrayList<Integer> users = new ArrayList<Integer>();
		
		int p_AD_User_ID = Env.getAD_User_ID(getCtx());
		int p_AD_Client_ID = Env.getAD_Client_ID(getCtx());
		
		for(int i = 0; i < bpartner.size(); i++)
		{
			sql.setLength(0);
			sql.append("SELECT AD_USER_ID FROM AD_User WHERE C_BPartner_ID = " + bpartner.get(i));
			
			users.add(DB.getSQLValue(get_TrxName(), sql.toString()));	
		}
		
		for(int i = 0; i < users.size(); i++)
		{
			if(time.get(i) > 7 || time.get(i) < 0)
				continue;
			
			if(users.get(i) < 0)
				continue;
			
			String message = "До окончания исполнения договора №" + documentno.get(i) + " осталось " + time.get(i) + " дней";
			
			X_R_MailText rText = new X_R_MailText(getCtx(), null, get_TrxName());
			
			rText.setMailHeader("Окончание исполнения договра");
			rText.setMailText(message);
			rText.setName("Zerde");
			
			if(!rText.save())
				throw new Exception("Unnable to save mail pattern");
			
			MMailText mailText = new MMailText(getCtx(), rText.get_ID(), get_TrxName());
			
			MUser userFrom = new MUser(getCtx(), p_AD_User_ID, get_TrxName());
			MUser userTo = new MUser(getCtx(), users.get(i), get_TrxName());		
			MClient client = new MClient(getCtx(), p_AD_Client_ID, get_TrxName());
			
			EMail mail = client.createEMail(userFrom, userTo, mailText.getMailHeader(), mailText.getMailText());			
			EMail.SENT_OK.equals(mail.send());						
		}
	}
}

